import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:music_player/models/song.dart';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;

  factory DatabaseService() => _instance;

  DatabaseService._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    print("_database");
    return _database!;
  }

  Future<Database> _initDatabase() async {
    print("_initDatabase");
    try{

      final String dataPath = await getDatabasesPath();
      print("_initDatabase path:$dataPath");
    } catch (e) {
      print("_initDatabase path:$e");
    }

    String path = join(await getDatabasesPath(), 'music_player.db');

    print("_initDatabase:$path");
    _database = await openDatabase(
      path,
      version: 2,
      onCreate: _onCreate,
      onUpgrade: (db, oldVersion, newVersion) async {
        if (oldVersion < 2) {
          await db.execute(
              'ALTER TABLE songs ADD COLUMN isDownloading INTEGER DEFAULT 0');
        }
      },
    );

    // 检查是否存在 "createdAt" 字段，如果不存在则创建
    List<Map> columns = await _database!.rawQuery("PRAGMA table_info(songs)");
    bool createdAtExists =
        columns.any((column) => column['name'] == 'createdAt');
    if (!createdAtExists) {
      await _database!
          .execute('ALTER TABLE songs ADD COLUMN createdAt DATETIME');
    }
    return _database!;
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE songs(
        songId TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        artist TEXT NOT NULL,
        downloadUrl TEXT NOT NULL,
        hot INTEGER DEFAULT 0,
        isDownloaded INTEGER DEFAULT 0,
        isDownloading INTEGER DEFAULT 0,
        localPath TEXT,
        lyrics TEXT,
        createdAt DATETIME
      )
    ''');
  }

  // 更新所有歌曲的热门状态
  Future<void> updateAllSongsHotStatus(bool status) async {
    final db = await database;
    print('updateAllSongsHotStatus: ${status}');
    await db.update(
      'songs',
      {'hot': status ? 1 : 0},
    );
  }

  // 删除非热门且未下载的歌曲
  Future<void> deleteNonHotUndownloadedSongs() async {
    final db = await database;
    await db.delete(
      'songs',
      where: 'hot = 0 AND isDownloaded = 0',
    );
  }

  // 获取所有歌曲，按热门优先排序
  Future<List<Song>> getAllSongsOrderedByHot() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'songs',
      orderBy: 'hot DESC',
    );
    return List.generate(maps.length, (i) => Song.fromJson(maps[i]));
  }

  Future<void> upsertSong(Song song) async {
    final db = await database;

    final exist = await db.query(
      'songs',
      where: 'songId = ?',
      whereArgs: [song.songId],
    );

    if (exist.isNotEmpty) {
      // 创建一个只包含非空值的 Map
      final updateData = <String, dynamic>{
        'hot': song.isHot ? 1 : 0,
      };

      await db.update(
        'songs',
        updateData,
        where: 'songId = ?',
        whereArgs: [song.songId],
      );
    } else {
      final insertData = {
        'songId': song.songId,
        'title': song.title,
        'artist': song.artist,
        'downloadUrl': song.downloadUrl,
        'hot': song.isHot ? 1 : 0,
        'isDownloaded': song.isDownloaded ? 1 : 0,
        'isDownloading': song.isDownloading ? 1 : 0,
        'localPath': song.localPath,
        'lyrics': song.lyrics,
        'createdAt': DateTime.now().toIso8601String(),
      };

      // 移除所有空值
      insertData.removeWhere((key, value) => value == null);

      await db.insert(
        'songs',
        insertData,
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
  }

  Future<void> deleteSong(String songId) async {
    final db = await database;
    await db.delete(
      'songs',
      where: 'songId = ?',
      whereArgs: [songId],
    );
  }
}
